//
//  WJDBProtocol.swift
//  PDFSign
//
//  Created by 李永彬 on 2025/9/19.
//

import Foundation
import FMDB

// MARK: - 数据库错误类型
enum WJDBError: Error {
    case databaseNotInitialized
    case tableCreationFailed
    case insertFailed
    case updateFailed
    case deleteFailed
    case queryFailed
    case databaseNotFound
    case invalidResultType
    case custom(message: String)
}

// MARK: - 数据库枚举
enum WJDatabase: String, CaseIterable {
    case pdfInfo = "pdf_info"
    
    var displayName: String {
        switch self {
        case .pdfInfo: return "主数据库"
        }
    }
    
    var databasePath: String {
        let documentsPath = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)[0]
        let databaseDirectory = documentsPath.appending("/DataBase")
        
        do {
            try createDirectoryIfNeeded(at: databaseDirectory)
        } catch {
            print("创建数据库目录失败: \(error.localizedDescription)")
            // 回退到临时目录
            return NSTemporaryDirectory().appending("\(self.rawValue).db")
        }
        
        return "\(databaseDirectory)/\(self.rawValue).db"
    }

    private func createDirectoryIfNeeded(at path: String) throws {
        let fileManager = FileManager.default
        
        var isDirectory: ObjCBool = false
        if fileManager.fileExists(atPath: path, isDirectory: &isDirectory) {
            if !isDirectory.boolValue {
                // 存在同名文件，删除后重新创建目录
                try fileManager.removeItem(atPath: path)
                try fileManager.createDirectory(atPath: path, withIntermediateDirectories: true)
            }
            // 已经是目录，直接返回
        } else {
            // 目录不存在，创建目录
            try fileManager.createDirectory(atPath: path, withIntermediateDirectories: true)
        }
    }
}

// MARK: - 数据库配置
struct WJDatabaseConfig {
    let database: WJDatabase
    var databaseQueue: FMDatabaseQueue?
    
    init(database: WJDatabase) {
        self.database = database
    }
}

// MARK: - 数据库查询结果协议
protocol WJDBQueryResult {
    init?(resultSet: FMResultSet)
}

// MARK: - 数据库操作协议
protocol WJDBProtocol: WJDBQueryResult {
    // 数据库相关属性
    static var associatedDatabase: WJDatabase { get }
    static var databaseConfig: WJDatabaseConfig { get set }
    static var tableName: String { get }
    
    // 必须实现的属性
    var id: Int { get set }
    
    // 初始化方法
    init()
    
    // 数据库操作
    static func setupDatabase() throws
    static func createTable() throws
    func save() throws -> Int
    func update() throws -> Bool
    func delete() throws -> Bool
    static func deleteAll() throws -> Bool
    
    // 查询方法（返回[WJDBQueryResult]）
    static func findAll() throws -> [WJDBQueryResult]
    static func find(byId id: Int) throws -> WJDBQueryResult?
    static func find(where condition: String, parameters: [Any]?) throws -> [WJDBQueryResult]
    
    // 需要子类实现的方法
    static func createTableSQL() -> String
    static func insertSQL(for object: Self) -> (String, [Any])
    static func updateSQL(for object: Self) -> (String, [Any])
}

// MARK: - 协议默认实现
extension WJDBProtocol {
    // 设置数据库
    static func setupDatabase() throws {
        if databaseConfig.databaseQueue == nil {
            let config = WJDBManager.shared.getOrCreateConfig(for: associatedDatabase)
            databaseConfig = config
        }
        
        guard databaseConfig.databaseQueue != nil else {
            throw WJDBError.databaseNotInitialized
        }
        
        try createTable()
    }
    
    // 关闭当前数据库
    static func closeCurrentDatabase() {
        databaseConfig.databaseQueue?.close()
        databaseConfig.databaseQueue = nil
        WJDBManager.shared.updateDatabaseConfig(databaseConfig, for: associatedDatabase)
    }
    
    // 获取数据库队列
    private static func getDatabaseQueue() throws -> FMDatabaseQueue {
        guard let queue = databaseConfig.databaseQueue else {
            throw WJDBError.databaseNotInitialized
        }
        return queue
    }
    
    // 保存对象
    func save() throws -> Int {
        if id > 0 {
            return try update() ? id : -1
        } else {
            return try insert()
        }
    }
    
    // 插入记录
    func insert() throws -> Int {
        let queue = try Self.getDatabaseQueue()
        
        var newId: Int64 = -1
        var error: Error?
        
        queue.inTransaction { db, rollback in
            do {
                let (sql, parameters) = Self.insertSQL(for: self)
                try db.executeUpdate(sql, values: parameters)
                newId = db.lastInsertRowId
            } catch let e {
                error = e
                rollback.pointee = true
            }
        }
        
        if let error = error {
            throw error
        }
        
        return Int(newId)
    }
    
    // 更新记录
    func update() throws -> Bool {
        let queue = try Self.getDatabaseQueue()
        
        var success = false
        var error: Error?
        
        queue.inTransaction { db, rollback in
            do {
                let (sql, parameters) = Self.updateSQL(for: self)
                try db.executeUpdate(sql, values: parameters)
                success = true
            } catch let e {
                error = e
                rollback.pointee = true
                success = false
            }
        }
        
        if let error = error {
            throw error
        }
        
        return success
    }
    
    // 删除记录
    func delete() throws -> Bool {
        let queue = try Self.getDatabaseQueue()
        
        var success = false
        var error: Error?
        
        queue.inTransaction { db, rollback in
            do {
                let sql = "DELETE FROM \(Self.tableName) WHERE id = ?"
                try db.executeUpdate(sql, values: [self.id])
                success = true
            } catch let e {
                error = e
                rollback.pointee = true
                success = false
            }
        }
        
        if let error = error {
            throw error
        }
        
        return success
    }
    
    // 删除所有记录
    static func deleteAll() throws -> Bool {
        let queue = try getDatabaseQueue()
        
        var success = false
        var error: Error?
        
        queue.inTransaction { db, rollback in
            do {
                let sql = "DELETE FROM \(tableName)"
                try db.executeUpdate(sql, values: nil)
                success = true
            } catch let e {
                error = e
                rollback.pointee = true
                success = false
            }
        }
        
        if let error = error {
            throw error
        }
        
        return success
    }
    
    // 查询所有记录
    static func findAll() throws -> [WJDBQueryResult] {
        return try find(where: "1 = 1", parameters: nil)
    }
    
    // 根据ID查询
    static func find(byId id: Int) throws -> WJDBQueryResult? {
        let results = try find(where: "id = ?", parameters: [id])
        return results.first
    }
    
    // 条件查询
    static func find(where condition: String, parameters: [Any]?) throws -> [WJDBQueryResult] {
        let queue = try getDatabaseQueue()
        
        var results: [WJDBQueryResult] = []
        var error: Error?
        
        queue.inDatabase { db in
            do {
                let sql = "SELECT * FROM \(tableName) WHERE \(condition)"
                let resultSet = try db.executeQuery(sql, values: parameters)
                
                while resultSet.next() {
                    if let object = Self(resultSet: resultSet) {
                        results.append(object)
                    }
                }
                resultSet.close()
            } catch let e {
                error = e
            }
        }
        
        if let error = error {
            throw error
        }
        
        return results
    }
    
    // 获取记录数量
    static func count(where condition: String? = nil, parameters: [Any]? = nil) throws -> Int {
        let queue = try getDatabaseQueue()
        
        var count = 0
        var error: Error?
        
        queue.inDatabase { db in
            do {
                var sql = "SELECT COUNT(*) FROM \(tableName)"
                if let condition = condition {
                    sql += " WHERE \(condition)"
                }
                
                let resultSet = try db.executeQuery(sql, values: parameters)
                if resultSet.next() {
                    count = Int(resultSet.int(forColumnIndex: 0))
                }
                resultSet.close()
            } catch let e {
                error = e
            }
        }
        
        if let error = error {
            throw error
        }
        
        return count
    }
    
    // 创建表
    static func createTable() throws {
        let queue = try getDatabaseQueue()
        
        var error: Error?
        var success = false
        
        queue.inTransaction { db, rollback in
            do {
                let sql = createTableSQL()
                try db.executeUpdate(sql, values: nil)
                success = true
            } catch let e {
                error = e
                rollback.pointee = true
                success = false
            }
        }
        
        if let error = error {
            throw error
        }
        
        if !success {
            throw WJDBError.tableCreationFailed
        }
    }
    
    // 获取当前数据库信息
    static var currentDatabase: WJDatabase {
        return associatedDatabase
    }
    
    static var currentDatabaseName: String {
        return associatedDatabase.displayName
    }
    
    // 类型转换辅助方法
    static func convertToTypedResults(_ results: [WJDBQueryResult]) throws -> [Self] {
        return results.compactMap { $0 as? Self }
    }
    
    static func convertToTypedResult(_ result: WJDBQueryResult?) throws -> Self? {
        return result as? Self
    }
}
